<?php
/**
 * RedCross Application
 *
 * @copyright Copyright (c) 2010, Kristian Stokke Tryggestad, Rune Vikestad, Andreas Flaten Wist, Simen Nicolai Echholt, Ole Henrik Jahren
 */

/**
 * Member gateway class for communicating with persistent storage
 *
 * @category    RedCross
 * @package     Core_Gateway
 */
class Core_Gateway_Member extends RedCross_Gateway_GatewayAbstract
{

  /**
   * Constant value to pass to the fetchEligibleForRole function if
   * is to ignore whether a user is passive or not
   * @var string
   */
  const ELIGIBLE_OPTION_IGNORE_PASSIVE = 'ignore_passive';

  /**
   * Constant value to pass to the fetchEligibleForRole function if
   * is to ignore whether a user is permanently passive or not
   * @var string
   */
  const ELIGIBLE_OPTION_IGNORE_PERMANENT_PASSIVE = 'ignore_permanent_passive';

  /**
   * Constant value to pass to the fetchEligibleForRole function if
   * is to ignore whether a user has the required courses or not
   * @var string
   */
  const ELIGIBLE_OPTION_IGNORE_COURSES = 'ignore_courses';

  /**
   * Constant value to pass to the fetchEligilbleForRole function if
   * is to only show users that have agreed to be seen by other members
   * @var string
   */
  const SHOW_ONLY_PUBLIC_USERS = 'public_users';



    /**
     * Constructor
     * Defines some private variables
     */
    public function __construct()
    {
        $this->_class = 'Member';
        $this->_modelPath = 'Core_Model';
    }

    public function delete($memberId)
    {
        $date = new RedCross_Date();
        $query = Doctrine_Query::create()
            ->update($this->getModelClass())
            ->set('quit_date', '?', $date->export())
            ->where('id = ?', $memberId);

        return $query->execute();
    }

    public function fetchWithPhoneAndEmail($id = null)
    {
    $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->leftJoin('m.Phonenumbers p')
            ->leftJoin('p.Phonetype pt')
            ->leftJoin('m.Postal po')
            ->where('m.id = ?', $id)
            ->orderBy('p.phonetype_id, e.is_main DESC')
            ->fetchOne();
        return $result;
    }

    /**
     * Fetch a memeber by an email
     * @param string $email
     * @return Core_Model_Member
     */
    public function fetchByEmail($email)
    {
        $email = (string) $email;
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->where('e.address = ?', $email)
            ->fetchOne();

        return $result;
    }

    public function fetchById($id)
    {
        $id = (string) $id;
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->where($this->getPrimaryKey() . ' = ?', $id)
            ->fetchOne();

        return $result;
    }

    /**
     * Fetches all members and joins with phone and email
     * @return Doctrine_Collection
     */
    public function fetchAllAndJoin($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->leftJoin('m.Phonenumbers p')
            ->leftJoin('m.User u')
            ->orderBy('m.last_name ASC, m.first_name ASC, e.is_main DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->execute();
    }

    public function fetchAllActive($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->leftJoin('m.Phonenumbers p')
            ->leftJoin('m.User u')
            ->addWhere('m.quit_date IS NULL')
            ->orderBy('m.last_name ASC, m.first_name ASC, e.is_main DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->execute();
    }

    public function fetchAllInactive($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->leftJoin('m.Phonenumbers p')
            ->leftJoin('m.User u')
            ->addWhere('m.quit_date IS NOT NULL')
            ->orderBy('m.last_name ASC, m.first_name ASC, e.is_main DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->execute();
    }
    
    public function fetchAllOrderByLastnameInactiveLast($paginate = false)
    {
        $query = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.emails e')
            ->leftJoin('m.Phonenumbers p')
            ->leftJoin('m.User u')
            ->orderBy('m.quit_date IS NULL DESC, m.last_name ASC, m.first_name ASC, e.is_main DESC');

        return $paginate ? new RedCross_Paginator($query) : $query->execute();
    }

    /*
     * Fetches member by ID and also fetches all courses the member has
     */
     public function fetchByIdJoinOnCourse($id)
     {
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.MemberCourse mc')
            ->leftJoin('mc.Course c')
            ->where('m.id = ?',$id)
            ->fetchOne();
        return $result;
     }

     public function fetchByIdJoinOnAccess($id)
     {
        $result = Doctrine_Query::create()
            ->select()
            ->from($this->getModelClass() . ' m')
            ->leftJoin('m.MemberAccess ma')
            ->leftJoin('ma.Access a')
            ->where('m.id = ?',$id)
            ->fetchOne();
        return $result;
     }

  public function fetchEligibleForRole($shiftRole, $options)
  {
    if (!is_object($shiftRole))
    {
      throw new RedCross_Exception('Angitt ShiftRole ikke funnet');
    }
    $escapeMaker = Doctrine_Manager::getInstance()->getCurrentConnection();
    $event_id = $escapeMaker->quoteIdentifier($shiftRole->Shift->event_id);
    $role_id = $escapeMaker->quoteIdentifier($shiftRole->role_id);
    $shift_date = $escapeMaker->quoteIdentifier($shiftRole->Shift->from_date);
    $shiftDate = new RedCross_Date($shift_date);
    $shiftDate->setHour(0);
    $shiftDate->setMinute(0);
    $shiftDate->setSecond(0);
    $shift_date = $shiftDate->export();

    $dateLimit = new RedCross_Date();

    /**** Attributes that affects the prioritization of members ****/

        $config = Zend_Registry::get('config');

    /*
     * How long back are we gonna check what shifts a member attended?
     * In other words, only shifts happening later than $dateLimitValue $dateLimitPart ago
     * will be concidered.
     */
    $dateLimitValue = $config['member']['fetchEligibleForRole']['dateLimitValue'];
    $dateLimitPart = RedCross_Date::MONTH;

    /*
     * How heavily should attendance be weighed compared to how many minutes the
     * member has worked
     */
    $attendanceWeight = $config['member']['fetchEligibleForRole']['attendanceWeight'];

    /*
     * See $attendanceWeight
     */
    $minutesWeight = 1 - $attendanceWeight;

    /**** End attributes that affects the prioritization of members ****/

    $dateLimit->sub($dateLimitValue,$dateLimitPart);
    $dateLimit = $dateLimit->export();

    $ignorePermanentPassive = (array_key_exists(self::ELIGIBLE_OPTION_IGNORE_PERMANENT_PASSIVE, $options) && $options[self::ELIGIBLE_OPTION_IGNORE_PERMANENT_PASSIVE]);
    $ignorePassive = (array_key_exists(self::ELIGIBLE_OPTION_IGNORE_PASSIVE, $options) && $options[self::ELIGIBLE_OPTION_IGNORE_PASSIVE]);
    $ignoreCourses = (array_key_exists(self::ELIGIBLE_OPTION_IGNORE_COURSES, $options) && $options[self::ELIGIBLE_OPTION_IGNORE_COURSES]);
    $showOnlyPublic = (array_key_exists(self::SHOW_ONLY_PUBLIC_USERS, $options) && $options[self::SHOW_ONLY_PUBLIC_USERS]);

    $query = Doctrine_Query::create()
    ->select('m.id, m.first_name, m.last_name')

    ->addSelect('(SELECT COUNT(*) FROM RedCross_Gateway_Base_Interest i WHERE i.member_id = m.id AND i.event_id = ' . $event_id . ')
      AS interest
    ')
    ->addSelect('(SELECT COUNT(*) FROM RedCross_Gateway_Base_PermanentPassive pp WHERE pp.member_id = m.id)
      AS is_permanent_passive
    ')
    ->addSelect('(SELECT COUNT(*) FROM RedCross_Gateway_Base_Passive p WHERE p.member_id = m.id AND p.date = \'' . $shift_date . '\')
      AS is_passive
    ')
    ->addSelect('(SELECT COUNT(*) FROM RedCross_Gateway_Base_RoleCourse rc2 WHERE rc2.role_id = ' . $role_id . ')
      AS required_course_count
    ')
    ->addSelect('(SELECT COUNT(*) FROM RedCross_Gateway_Base_MemberCourse mc WHERE mc.member_id = m.id AND (mc.expires_date IS NULL OR mc.expires_date >= \''. $shift_date . '\') AND mc.course_id IN (SELECT rc.course_id FROM RedCross_Gateway_Base_RoleCourse rc WHERE rc.role_id = ' . $role_id . '))
      AS course_count
    ')
    ->addSelect('
      (
        (
          (SELECT COUNT(DISTINCT shift_id) FROM RedCross_Gateway_Base_ShiftRole sr WHERE ((sr.attended_member_id = m.id OR (sr.member_id = m.id AND (SELECT s5.shift_report_id FROM RedCross_Gateway_Base_Shift s5 WHERE s5.id = sr.shift_id) IS NULL))) AND sr.shift_id IN (SELECT s2.id FROM RedCross_Gateway_Base_Shift s2 WHERE s2.from_date >= \'' . $dateLimit . '\'))
          /
          (SELECT COUNT(*) FROM RedCross_Gateway_Base_ShiftRole sr4 WHERE sr4.shift_id IN (SELECT s3.id FROM RedCross_Gateway_Base_Shift s3 WHERE s3.from_date >= \'' . $dateLimit . '\'))
        )
        *
        ' . $attendanceWeight . '
      )
      AS weight_vakter
    ')
    ->addSelect('
      (
        (
          (SELECT SUM(UNIX_TIMESTAMP(s.to_date) - UNIX_TIMESTAMP(s.appearance_time)) FROM RedCross_Gateway_Base_Shift s WHERE (s.from_date >= \'' . $dateLimit . '\' AND s.id IN (SELECT sr2.shift_id FROM RedCross_Gateway_Base_ShiftRole sr2 WHERE (sr2.attended_member_id = m.id) OR (sr2.member_id = m.id AND s.shift_report_id IS NULL))))
          /
          (SELECT SUM(UNIX_TIMESTAMP(s4.to_date) - UNIX_TIMESTAMP(s4.appearance_time)) FROM RedCross_Gateway_Base_Shift s4 WHERE (s4.from_date >= \'' . $dateLimit . '\')
        )
        *
        ' . $minutesWeight . ')
      )
      AS weight_minutter
      '
    )
    ->from('RedCross_Gateway_Base_Member m')
    ->leftJoin('m.User u')
    ->where('m.quit_date IS NULL')
    ->andWhere('u.is_confirmed = 1')
    ->groupBy('m.id');

    if (!$ignorePermanentPassive)
    {
      $query->addHaving('is_permanent_passive = 0');
    }

    if (!$ignorePassive)
    {
      $query->addHaving('is_passive = 0');
    }

    if (!$ignoreCourses)
    {
      $query->addHaving('course_count = required_course_count');
    }

    if ($showOnlyPublic) {
        $query->andWhere('m.is_public = 1');
    }

    $query->orderBy('interest DESC, weight_vakter + weight_minutter ASC, m.first_name, m.last_name');

    $result = $query->fetchArray();

    //The sort order of people with interest for the event has to be reversed
    //because the members with the highest attendance should be prioritized
    $interestedMembers = array();
    for ($i = 0; $i < count($result); $i++)
    {
      $result[$i]['weight'] = (int)(($result[$i]['weight_minutter'] * 10000) + ($result[$i]['weight_vakter'] * 10000));
      if ((bool)$result[$i]['interest'])
      {
        $interestedMembers[] = $result[$i];
      }
    }
    $interestedMembersProperlySorted = array_reverse($interestedMembers);
    for ($i = 0; $i < count($interestedMembersProperlySorted); $i++)
    {
      $result[$i] = $interestedMembersProperlySorted[$i];
    }
    return $result;
  }

    /*
     * Returns an array with passive messages between the given dates,
     * or all passive messages if the parameters are not specified.
     */
    public function getPassiveMessages($member_id, $from_date, $to_date)
    {
      $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Passive p')
            ->where('p.member_id = ?', $member_id);

      if (!is_null($from_date)) {
            $query->addWhere('p.date >= ?', $from_date);
      }
      if (!is_null($to_date)) {
        $query->addWhere('p.date < ?', $to_date);
      }

      return $query->execute();
    }

    /**
     * @param unknown_type $memberId
     * @return boolean
     */
    public function isPermanentPassive($memberId = null)
    {
      $query = Doctrine_Query::create()
        ->select()
            ->from('Core_Model_PermanentPassive p')
            ->where('p.member_id = ?', $memberId);

        return count($query->execute()) == 1;
    }

    public function removePermanentPassive($memberId)
    {
    $query = Doctrine_Query::create()
            ->delete('Core_Model_PermanentPassive p')
            ->where('p.member_id = ?', $memberId);

        return count($query->execute());
    }

    public function addPermanentPassive($memberId, $comment)
    {
        $ppMessage = new Core_Model_PermanentPassive();
        $ppMessage->comment = $comment;
        $ppMessage->member_id = $memberId;
        $ppMessage->save();

        return true;
    }

    public function removePassiveMessages($memberId, $fromDate, $toDate)
    {
  $query = Doctrine_Query::create()
      ->delete('Core_Model_Passive p')
      ->where('p.member_id = ?', $memberId)
      ->andWhere('p.date >= ?', $fromDate)
      ->andWhere('p.date <= ?', $toDate);

  return $query->execute();
    }

    public function fetchMembersWithCourse($courseId) {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Member m')
            ->where('mc.course_id = ?', $courseId)
            ->leftJoin('m.MemberCourse mc ON m.id=mc.member_id');

        return $query->execute();
    }

    public function fetchMembersWithAccess($accessId) {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Member m')
            ->where('ma.access_id = ?', $accessID)
            ->leftJoin('m.MemberAccess ma ON m.id=ma.member_id');
        return $query->execute();
    }
    public function fetchByUsername($username) {
        $query = Doctrine_Query::create()
            ->select()
            ->from('Core_Model_Member m')
            ->leftJoin('m.User u')
            ->where('u.username = ?', $username);
        return $query->fetchOne();
    }
}
